If you have an indexed SAS^® Scalable Performance Data Server (SPD Server) table or SPD Server cluster of indexed tables, then certain queries unexpectedly return no results. This problem occurs when the indexed column contains the same value for all rows in a particular table.

The issue has been observed only when the query contains a WHERE clause that is similar to the following:

See the Full Code tab for example code to replicate this problem.

A workaround is to drop the index, and add a MINMAXVARLIST= table option. This option reduces the build time for the table or cluster of tables, reduces space requirements, and can improve performance. See the Full Code tab for example code that uses MINMAXVARLIST.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

This code shows an example of a single SPD Server table where the indexed column has the same value for all rows. In the subsequent query, you would expect all rows to be returned, but the result is that no rows are returned.

The Workaround section demonstrates the workaround of using MINMAXVARLIST when creating the table rather than creating an index.

Code to Replicate the Problem

* Libname is an example, and would need the schema, server, and user set appropriately ;
libname tmp sasspds SCHEMA='TMP' server=myserver.5400 user='anonymous' ;

* Create SPDS Table with Index, where the indexed column has the same value for all rows ;
data tmp.table (index=(number)) ; 
   do row = 1 to 10 ; number = 2 ; output ; end ;
run ;
* Demonstrate that no rows are selected ;
proc sql ; 
   create table test as 
      select * 
        from tmp.table 
       where number >= 2 and number <  4 ; 
quit ;

Workaround
* Create the SPDS Table with MINMAXVARLIST rather than an INDEX ;
data tmp.table (minmaxvarlist=(number)) ; 
   do row = 1 to 10 ; number = 2 ; output ; end ;
run ;
* Demonstrate that 10 rows are selected ;
proc sql ; 
   create table test as 
      select * 
        from tmp.table 
       where number >= 2 and number <  4 ; 
quit ;

A fix for this issue for SAS Scalable Performance Data Server 5.2 is available at:

A fix for this issue for SAS Scalable Performance Data Server 5.1 is available at: